﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;

namespace Battleship
{
    class ExcelReader
    {
        //Add this codes in your progam code
        private Microsoft.Office.Interop.Excel.Application appExcel;
        private Workbook newWorkbook = null;
        private _Worksheet objsheet = null;
        private Worksheet sheet = null;

        //Method to initialize opening Excel
        public void open(String path)
        {
            appExcel = new Microsoft.Office.Interop.Excel.Application();

            if (System.IO.File.Exists(path))
            {
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                sheet = (Worksheet)newWorkbook.Sheets[1];
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                Console.WriteLine("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;
            }
        }

        public void showValues()
        {
            Range exRange = sheet.UsedRange;
            int num_row = exRange.Rows.Count;
            int num_col = exRange.Columns.Count;
            for (int i = 1; i <= num_row; i++)
            {
                for (int j = 1; j <= num_col; j++)
                {
                    Console.Write(sheet.Cells[i, j].value + " ");
                }
                Console.WriteLine();
            }
        }

        //Method to get value; cellname is A1,A2, or B1,B2 etc...in excel.
        public string getValue(string cellname)
        {
            string value = string.Empty;
            try
            {
                value = objsheet.get_Range(cellname).get_Value().ToString();
            }
            catch
            {
                value = "";
            }
            return value;
        }

        // get value from cell(i, j)
        public string getValue(int row, int col)
        {
            return sheet.Cells[col, row].value.ToString();
        }

        //Method to close excel connection
        public void close()
        {
            if (appExcel != null)
            {
                try
                {
                    newWorkbook.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                    appExcel = null;
                    objsheet = null;
                }
                catch (Exception ex)
                {
                    appExcel = null;
                    Console.WriteLine("Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            }
        }

    }
}
